package com.szholly.data.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.core.namedparam.*;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.szholly.utils.exception.ArgumentException;
import com.szholly.utils.util.StringUtils;
import com.szholly.data.common.delegate.*;
import com.szholly.data.general.*;

/**
 * 类功能说明 定义基于ado的数据库工作空间访问类
 */
public abstract class DatabaseWorkspaceBase implements IWorkspace {

	private static Logger logger = Logger.getLogger(DatabaseWorkspaceBase.class);
	
	protected NamedParameterJdbcTemplate jdbcTemplate;
	protected JdbcTemplate innerJdbcTemplate;
	protected BasicDataSource dataSource;
	protected DataSourceTransactionManager transactionManager;
	protected ConnectionInfo info;

	private HashMap<String, String> insertSqlCache = new HashMap<String, String>();
	private HashMap<String, String> updateSqlCache = new HashMap<String, String>();
	
	/**
	 * 当前的事务处理状态
	 */
	private ThreadLocal<TransactionStatus> transactionStatus = new ThreadLocal<TransactionStatus>();

	/**
	 * 事务层数
	 */
	private ThreadLocal<Integer> transactionRegs = new ThreadLocal<Integer>();

	/**
	 * 在事务块中是否调用过Rollback
	 */
	private ThreadLocal<Boolean> hasInvokeRollback = new ThreadLocal<Boolean>();

	/**
	 * 构建工作空间
	 */
	public DatabaseWorkspaceBase(ConnectionInfo info) {
		this.info = info;
		this.dataSource = getDataSource(info);
		innerJdbcTemplate = new JdbcTemplate(dataSource);
		this.jdbcTemplate = new NamedParameterJdbcTemplate(innerJdbcTemplate);
		this.transactionManager = new DataSourceTransactionManager();
		this.transactionManager.setDataSource(dataSource);
	}

	/**
	 * 获取数据源
	 */
	protected abstract BasicDataSource getDataSource(ConnectionInfo info);

	/**
	 * 替换SQL语句，用于数据库的差异化执行
	 */
	protected abstract String replaceSql(String sql);

	/**
	 * 获取得到一个表的自动增值SQL
	 */
	@Override
	public abstract String getAutoSql(ITable table, String fieldName);

	@Override
	public WorkspaceType getWorkspaceType() {
		return info.getWorkspaceType();
	}

	@Override
	public String getDatabase() {
		return info.getDatabase();
	}

	@Override
	public String getNetName() {
		return info.getNetName();
	}

	@Override
	public String getServer() {
		return info.getServer();
	}

	@Override
	public String getUserID() {
		return info.getUserID();
	}

	@Override
	public String getPassword() {
		return info.getPassword();
	}

	@Override
	public String getPort() {
		return info.getPort();
	}

	@Override
	public String getVersion() {
		return info.getVersion();
	}

	@Override
	public String getConnectionString() {
		return info.getConnectionStr();
	}

	@Override
	public void startEditing() {
		StartTransactions(transactionManager);
	}

	@Override
	public void stopEditing(boolean saveEdits) {
		StopTransactions(transactionManager, saveEdits);
	}

	@Override
	public boolean isBeingEdited() {
		return HasNextTransactions();
	} 
	
	/**
	 * 开始一个事务
	 */
	protected void StartTransactions(
			DataSourceTransactionManager transactionManager) {
		TransactionStatus status = transactionStatus.get();
		if (status == null) {
			// 开始一个事务
			DefaultTransactionDefinition definition = new DefaultTransactionDefinition();
			definition.setTimeout(30);
			status = transactionManager.getTransaction(definition);
			transactionStatus.set(status);
			transactionRegs.set(1);
			hasInvokeRollback.set(false);
			String tranName = "[" + this.hashCode() + "_"
					+ Thread.currentThread().getId() + "]";
			logger.info("【StartTransactions】 事务" + tranName + "开始");
		} else {
			// 说明上级存在注册的事务块，当前块不启做用
			Integer curInteger = transactionRegs.get();
			curInteger++;
			transactionRegs.set(curInteger);
		}
	}

	/**
	 * 停止一个事务
	 */
	protected void StopTransactions(
			DataSourceTransactionManager transactionManager, boolean saveEdits) {
		// 不存在事务
		TransactionStatus status = transactionStatus.get();
		if (status == null) {
			return;
		}

		Integer curInteger = transactionRegs.get();
		curInteger--;
		transactionRegs.set(curInteger);
		if (transactionRegs.get() == 0) {
			if (hasInvokeRollback.get())
				saveEdits = false;

			boolean hasError = false;
			try{
				if (saveEdits) {
					transactionManager.commit(status);
				} else {
					transactionManager.rollback(status);
				}
			}catch(Exception e){
				hasError = true;
				e.printStackTrace();
			}finally{
				String tranName = "[" + this.hashCode() + "_"
						+ Thread.currentThread().getId() + "]";
				if (saveEdits) {
					if(hasError){
						logger.info("【StopTransactions】 事务" + tranName + "提交失败");
					}else{
						logger.info("【StopTransactions】 事务" + tranName + "提交成功");
					}
				} else {
					if(hasError){
						logger.info("【StopTransactions】 事务" + tranName + "回滚失败");
					}else{
						logger.info("【StopTransactions】 事务" + tranName + "回滚成功");
					}
				}
				transactionStatus.remove();
				transactionRegs.remove();
				hasInvokeRollback.remove();
			}
		} else {
			if (!saveEdits)
				hasInvokeRollback.set(true);
		}
	}

	/**
	 * 是否还存在下一个事务块
	 */
	protected boolean HasNextTransactions() {
		TransactionStatus status = transactionStatus.get();
		if (status == null) {
			return false;
		}
		Integer curInteger = transactionRegs.get();
		if (curInteger <= 0)
			return false;
		else
			return true;
	}

	/**
	 * 执行SQL语句返回查询结果IDataReader
	 * 
	 * @param strSql
	 * @param para
	 * @param dataDelegate
	 */
	@SuppressWarnings({ "unchecked" })
	@Override
	public <T extends RowBase> void getDataReader(Class<T> c, String strSql,
			GlobalParameter[] para, DataReaderDelegate dataDelegate) {
		if (dataDelegate == null) {
			return;
		}
		strSql = this.replaceSql(strSql);
		SqlParameterSource source = ParameterHelper.BindParameter(para);
		logger.info("【getDataReader】 " + strSql);
		dataReaderResultSetExtractor<T> rse = new dataReaderResultSetExtractor<T>(
				c, dataDelegate);
		jdbcTemplate.query(strSql, source, rse);
	}

	@SuppressWarnings("rawtypes")
	private class dataReaderResultSetExtractor<T extends RowBase> implements
			ResultSetExtractor {

		private DataReaderDelegate dataDelegate;
		private Class<T> c;

		public dataReaderResultSetExtractor(Class<T> c,
				DataReaderDelegate dataDelegate) {
			super();
			this.dataDelegate = dataDelegate;
			this.c = c;
		}

		@Override
		public Object extractData(ResultSet resultSet) throws SQLException,
				DataAccessException {
			if (dataDelegate != null) {
				DbDataReader<T> reader = new DbDataReader<T>(c, resultSet);
				dataDelegate.invokeDataReader(reader, null);
			}
			return null;
		}
	}

	/**
	 * 执行SQL语句返回查询结果IDataReader
	 * 
	 * @param tableName
	 * @param queryFilter
	 * @param dataDelegate
	 */
	@Override
	public <T extends RowBase> void getDataReader(Class<T> c, ITable pTable,
			IQueryFilter queryFilter, DataReaderDelegate dataDelegate) {
		String sql = null;
		if (queryFilter == null) {
			sql = "select " + pTable.getSelectFields(false) + " from "
					+ pTable.getTableName();
			getDataReader(c, sql, GlobalParameter.Empty, dataDelegate);
		} else {
			sql = queryFilter.toSql(pTable);
			getDataReader(c, sql, queryFilter.getWhereParameter(), dataDelegate);
		}
	}

	/**
	 * 执行SQL语句返回查询结果IDataReader
	 * 
	 * @param tableName
	 * @param queryMap
	 * @param dataDelegate
	 */
	@Override
	public <T extends RowBase> void getDataReader(Class<T> c, ITable pTable,
			IQueryMap queryMap, DataReaderDelegate dataDelegate) {
		String sql = null;
		if (queryMap == null) {
			sql = "select " + pTable.getSelectFields(false) + " from "
					+ pTable.getTableName();
			getDataReader(c, sql, GlobalParameter.Empty, dataDelegate);
		} else {
			IQueryFilter queryFilter = queryMap.toQueryFilter(pTable);
			sql = queryFilter.toSql(pTable);
			getDataReader(c, sql, queryFilter.getWhereParameter(), dataDelegate);
		}
	}

	/**
	 * 返回DataReader数据
	 */
	@Override
	public <T extends RowBase> void getDataReader(Class<T> c, ITable pTable,
			RowBase queryRow, DataReaderDelegate dataDelegate) {
		String sql = null;
		if (queryRow == null) {
			sql = "select " + pTable.getSelectFields(false) + " from "
					+ pTable.getTableName();
			getDataReader(c, sql, GlobalParameter.Empty, dataDelegate);
		} else {
			IQueryFilter queryFilter = queryRow.toQueryFilter();
			sql = queryFilter.toSql(pTable);
			getDataReader(c, sql, queryFilter.getWhereParameter(), dataDelegate);
		}
	}

	/**
	 * 执行SQL语句返回查询结果object
	 * 
	 * @param sqlString
	 * @param para
	 * @return
	 */
	@Override
	public Object getExecuteScalar(String sqlString, GlobalParameter[] para) {
		sqlString = this.replaceSql(sqlString);
		SqlParameterSource source = ParameterHelper.BindParameter(para);
		logger.info("【getExecuteScalar】 " + sqlString);
		return jdbcTemplate.queryForObject(sqlString, source, Object.class);
	}

	/**
	 * 执行SQL语句返回查询结果object
	 * 
	 * @param queryFilter
	 * @return
	 */
	@Override
	public Object getExecuteScalar(ITable pTable, IQueryFilter queryFilter) {
		String sql = null;
		if (queryFilter == null) {
			sql = "select " + pTable.getSelectFields(false) + " from "
					+ pTable.getTableName();
			return getExecuteScalar(sql, GlobalParameter.Empty);
		} else {
			sql = queryFilter.toSql(pTable);
			return getExecuteScalar(sql, queryFilter.getWhereParameter());
		}
	}

	/**
	 * 执行SQL语句返回查询结果object
	 * 
	 * @param queryFilter
	 * @return
	 */
	@Override
	public Object getExecuteScalar(ITable pTable, IQueryMap queryMap) {
		String sql = null;
		if (queryMap == null) {
			sql = "select " + pTable.getSelectFields(false) + " from "
					+ pTable.getTableName();
			return getExecuteScalar(sql, GlobalParameter.Empty);
		} else {
			IQueryFilter queryFilter = queryMap.toQueryFilter(pTable);
			sql = queryFilter.toSql(pTable);
			return getExecuteScalar(sql, queryFilter.getWhereParameter());
		}
	}

	/**
	 * 执行SQL语句返回查询结果object
	 * 
	 * @param queryFilter
	 * @return
	 */
	@Override
	public Object getExecuteScalar(ITable pTable, RowBase queryRow) {
		String sql = null;
		if (queryRow == null) {
			sql = "select " + pTable.getSelectFields(false) + " from "
					+ pTable.getTableName();
			return getExecuteScalar(sql, GlobalParameter.Empty);
		} else {
			IQueryFilter queryFilter = queryRow.toQueryFilter();
			sql = queryFilter.toSql(pTable);
			return getExecuteScalar(sql, queryFilter.getWhereParameter());
		}
	}

	/**
	 * 获取实体类，根据实体信息
	 */
	@Override
	public <T extends RowBase> T getEntity(Class<T> c, ITable pTable,
			RowBase queryRow) {
		IQueryFilter queryFilter = null;
		if (queryRow != null) {
			queryFilter = queryRow.toQueryFilter();
		}
		return getEntity(c, pTable, queryFilter);
	}

	/**
	 * 获取实体类，根据查询信息
	 * 
	 * @param queryFilter
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public <T extends RowBase> T getEntity(Class<T> c, ITable pTable,
			IQueryFilter queryFilter) {
		DataReaderDelegate dataReaderDelegate = new DataReaderDelegateClass();
		T entity = null;
		getDataReader(c, pTable, queryFilter, dataReaderDelegate);
		entity = (T) dataReaderDelegate.paraObject;
		return entity;
	}

	private class DataReaderDelegateClass extends DataReaderDelegate {
		@Override
		public void invokeDataReader(IDataReader reader, Object tag) {
			RowBase t = null;
			if (reader.read()) {
				t = reader.getRow();
			}
			paraObject = t;
		}
	}

	/**
	 * 获取实体类，根据查询信息
	 * 
	 * @param queryFilter
	 * @return
	 */
	@Override
	public <T extends RowBase> T getEntity(Class<T> c, ITable pTable,
			IQueryMap queryMap) {
		IQueryFilter queryFilter = null;
		if (queryMap != null) {
			queryFilter = queryMap.toQueryFilter(pTable);
		}
		return getEntity(c, pTable, queryFilter);
	}

	/**
	 * 执行SQL语句返回查询结果List<RowBase>
	 * 
	 * @param sqlString
	 * @param para
	 * @return
	 */
	@Override
	public <T extends RowBase> List<T> getEntityList(Class<T> c, String strSql,
			GlobalParameter[] para) {
		strSql = this.replaceSql(strSql);
		SqlParameterSource source = ParameterHelper.BindParameter(para);
		
		StringBuilder parmStr = new StringBuilder();
		if(para != null){
			for(GlobalParameter item:para){
				parmStr.append(item.getParaName()+"="+item.getParaValue()+" AND ");
			}
		}
		
		logger.info("\n【getEntityList】 " + strSql + " \n 【参数】 " + parmStr.toString());
		List<Map<String, Object>> listItem = jdbcTemplate.queryForList(strSql,
				source);
		List<T> result = new ArrayList<T>();

		try {
			for (Map<String, Object> item : listItem) {
				T rowItem = null;
				rowItem = c.newInstance();
				rowItem.setIsNew(false);
				rowItem.putAll(item);
				result.add(rowItem);
			}
		} catch (InstantiationException e) {
			e.printStackTrace();
		}catch( IllegalAccessException ex){
			ex.printStackTrace();
		}
		return result;
	}

	/**
	 * 执行SQL语句返回查询结果List<RowBase>
	 * 
	 * @param queryFilter
	 * @return
	 */
	@Override
	public <T extends RowBase> List<T> getEntityList(Class<T> c, ITable pTable,
			IQueryFilter queryFilter) {
		String sql = null;
		if (queryFilter == null) {
			sql = "select " + pTable.getSelectFields(false) + " from "
					+ pTable.getTableName();
			return getEntityList(c, sql, GlobalParameter.Empty);
		} else {
			sql = queryFilter.toSql(pTable);
			return getEntityList(c, sql, queryFilter.getWhereParameter());
		}
	}

	/**
	 * 执行SQL语句返回查询结果List<RowBase>
	 * 
	 * @param queryFilter
	 * @return
	 */
	@Override
	public <T extends RowBase> List<T> getEntityList(Class<T> c, ITable pTable,
			IQueryMap queryMap) {
		String sql = null;
		if (queryMap == null) {
			sql = "select " + pTable.getSelectFields(false) + " from "
					+ pTable.getTableName();
			return getEntityList(c, sql, GlobalParameter.Empty);
		} else {
			IQueryFilter queryFilter = queryMap.toQueryFilter(pTable);
			sql = queryFilter.toSql(pTable);
			return getEntityList(c, sql, queryFilter.getWhereParameter());
		}
	}

	/**
	 * 执行SQL语句返回查询结果List<RowBase>
	 * 
	 * @param row
	 * @return
	 */
	@Override
	public <T extends RowBase> List<T> getEntityList(Class<T> c, ITable pTable,
			RowBase row) {
		String sql = null;
		if (row == null) {
			sql = "select " + pTable.getSelectFields(false) + " from "
					+ pTable.getTableName();
			return getEntityList(c, sql, GlobalParameter.Empty);
		} else {
			IQueryFilter queryFilter = row.toQueryFilter();
			sql = queryFilter.toSql(pTable);
			return getEntityList(c, sql, queryFilter.getWhereParameter());
		}
	}

	/**
	 * 分页查询
	 * 
	 * @param sql
	 * @param para
	 * @param pageInfo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public <T extends RowBase> List<T> pageSelect(Class<T> c, String sql,
			GlobalParameter[] para, PageInfo pageInfo) {
		sql = replaceSql(sql);
		logger.info("【pageSelect】 " + sql);
		int startIndex = (pageInfo.getPageIndex() - 1) * pageInfo.getPageSize();
		SplitPageResultSetExtractor<T> splitPageExtractor = new SplitPageResultSetExtractor<T>(
				c, startIndex, pageInfo.getPageSize(), pageInfo.getRefreshRowCount());
		CustomStatementCreator psc = new CustomStatementCreator(sql, para);
		List<T> result = (List<T>) innerJdbcTemplate.query(psc, splitPageExtractor);
		pageInfo.setRowCount(splitPageExtractor.rowCount);
		return result;
	}

	/**
	 * 定义分页查询对象
	 */
	private class CustomStatementCreator implements PreparedStatementCreator {
		private String sql;
		private GlobalParameter[] para;
		public CustomStatementCreator(String sql, GlobalParameter[] para) {
			this.sql = sql;
			this.para = para;
		}

		@Override
		public PreparedStatement createPreparedStatement(Connection conn)
				throws SQLException {
			for (GlobalParameter dbp : para) {
				String paraName = dbp.getParaName().replace("@", "").replace(":", "");
				sql = sql.replace("@"+paraName, "?").replace(":"+paraName, "?");
			}
			PreparedStatement pPreparedStatement = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			int i = 1;
			for (GlobalParameter dbp : para) {
				Object paraValue = dbp.getParaValue();
				pPreparedStatement.setObject(i, paraValue);
				i++;
			}
			return pPreparedStatement;
		}
	}

	/**
	 * 分页查询
	 * 
	 * @param tableName
	 * @param queryFilter
	 * @param pageInfo
	 * @return
	 */
	@Override
	public <T extends RowBase> List<T> pageSelect(Class<T> c, ITable pTable,
			IQueryFilter queryFilter, PageInfo pageInfo) {
		String sql = null;
		if (queryFilter == null) {
			sql = "select " + pTable.getSelectFields(false) + " from "
					+ pTable.getTableName();
			return pageSelect(c, sql, GlobalParameter.Empty, pageInfo);
		} else {
			sql = queryFilter.toSql(pTable);
			return pageSelect(c, sql, queryFilter.getWhereParameter(), pageInfo);
		}
	}

	/**
	 * 分页查询
	 * 
	 * @param tableName
	 * @param queryFilter
	 * @param pageInfo
	 * @return
	 */
	@Override
	public <T extends RowBase> List<T> pageSelect(Class<T> c, ITable pTable,
			IQueryMap queryMap, PageInfo pageInfo) {
		String sql = null;
		if (queryMap == null) {
			sql = "select " + pTable.getSelectFields(false) + " from "
					+ pTable.getTableName();
			return pageSelect(c, sql, GlobalParameter.Empty, pageInfo);
		} else {
			IQueryFilter queryFilter = queryMap.toQueryFilter(pTable);
			sql = queryFilter.toSql(pTable);
			return pageSelect(c, sql, queryFilter.getWhereParameter(), pageInfo);
		}
	}

	/**
	 * 分页查询
	 * 
	 * @param tableName
	 * @param queryFilter
	 * @param pageInfo
	 * @return
	 */
	@Override
	public <T extends RowBase> List<T> pageSelect(Class<T> c, ITable pTable,
			RowBase queryRow, PageInfo pageInfo) {
		String sql = null;
		if (queryRow == null) {
			sql = "select " + pTable.getSelectFields(false) + " from "
					+ pTable.getTableName();
			return pageSelect(c, sql, GlobalParameter.Empty, pageInfo);
		} else {
			IQueryFilter queryFilter = queryRow.toQueryFilter();
			sql = queryFilter.toSql(pTable);
			return pageSelect(c, sql, queryFilter.getWhereParameter(), pageInfo);
		}
	}

	/**
	 * 获取某列的最大值
	 * 
	 * @param fieldName
	 * @param otherCondition
	 * @param paras
	 * @return
	 */
	@Override
	public long getSpecFieldMaxValue(ITable pTable, String fieldName,
			String otherCondition, GlobalParameter[] paras) {
		// 定义默认返回值
		long result = -1;

		// 组织获取最大值SQL
		IQueryFilter queryFilter = new QueryFilter();
		queryFilter.setSelectFields("max(" + fieldName + ") as MAXFIELDNAME");
		queryFilter.setWhereString(otherCondition);
		queryFilter.setWhereParameter(paras);

		DataReaderDelegate dataReaderDelegate = new getGetSpecFieldMaxValue();
		getDataReader(RowBase.class, pTable, queryFilter, dataReaderDelegate);
		result = (Long) dataReaderDelegate.paraObject;
		return result;
	}

	private class getGetSpecFieldMaxValue extends DataReaderDelegate {
		@Override
		public void invokeDataReader(IDataReader reader, Object tag) {
			long result = 0;
			if (reader.read()) {
				Object obj = reader.getRow().getValue(GlobalDataType.Int64,
						"MAXFIELDNAME");
				if (null != obj) {
					result = Long.parseLong(obj.toString());
				}
			}
			this.paraObject = result;
		}
	}

	/**
	 * 获取某列的最小值
	 * 
	 * @param fieldName
	 * @param otherCondition
	 * @param paras
	 * @return
	 */
	@Override
	public long getSpecFieldMinValue(ITable pTable, String fieldName,
			String otherCondition, GlobalParameter[] paras) {
		// 定义默认返回值
		long result = -1;

		// 组织获取最大值SQL
		IQueryFilter queryFilter = new QueryFilter();
		queryFilter.setSelectFields("min(" + fieldName + ") as MINFIELDNAME");
		queryFilter.setWhereString(otherCondition);
		queryFilter.setWhereParameter(paras);

		DataReaderDelegate dataReaderDelegate = new getGetSpecFieldMinValue();
		getDataReader(RowBase.class, pTable, queryFilter, dataReaderDelegate);
		result = (Long) dataReaderDelegate.paraObject;
		return result;
	}

	private class getGetSpecFieldMinValue extends DataReaderDelegate {
		@Override
		public void invokeDataReader(IDataReader reader, Object tag) {
			long result = -1;
			if (reader.read()) {
				Object obj = reader.getRow().getValue(GlobalDataType.Int64,
						"MINFIELDNAME");
				result = Long.parseLong(obj.toString());
			}
			this.paraObject = result;
		}
	}

	/**
	 * 获取下一个要素，如果没有返回null
	 * 
	 * @param t
	 * @param byFieldName
	 * @param whereString
	 * @param orderByType
	 * @param addCaptionField
	 * @return
	 */
	@Override
	public <T extends RowBase> T getNextEntity(Class<T> c, ITable pTable,
			RowBase t, String byFieldName, String whereString,
			OrderByType orderByType, boolean addCaptionField) {
		if (t == null || StringUtils.IsNullOrEmpty(byFieldName))
			return null;

		IQueryFilter queryFilter = new QueryFilter();
		queryFilter.setAddCaptionField(addCaptionField);
		switch (orderByType) {
		case ASC:
			queryFilter.setWhereString(byFieldName + " > @curvalue ");
			queryFilter.setOrderByString(byFieldName + " ASC ");
			break;
		case DESC:
			queryFilter.setWhereString(byFieldName + " < @curvalue ");
			queryFilter.setOrderByString(byFieldName + " DESC ");
			break;
		}

		if (!StringUtils.IsNullOrSpace(whereString))
			queryFilter.setWhereString(queryFilter.getWhereString() + " and ("
					+ whereString + ") ");

		queryFilter
				.setWhereParameter(new GlobalParameter[] { new GlobalParameter(
						"@curvalue", ((RowBase) t).getValue(
								GlobalDataType.String, byFieldName)) });
		return getEntity(c, pTable, queryFilter);
	}

	/**
	 * 获取上一个要素，如果没有返回null
	 * 
	 * @param t
	 * @param byFieldName
	 * @param whereString
	 * @param orderByType
	 * @param addCaptionField
	 * @return
	 */
	@Override
	public <T extends RowBase> T getPerEntity(Class<T> c, ITable pTable,
			RowBase t, String byFieldName, String whereString,
			OrderByType orderByType, boolean addCaptionField) {
		if (t == null || StringUtils.IsNullOrEmpty(byFieldName))
			return null;

		IQueryFilter queryFilter = new QueryFilter();
		queryFilter.setAddCaptionField(addCaptionField);
		switch (orderByType) {
		case ASC:
			queryFilter.setWhereString(byFieldName + " < @curvalue ");
			queryFilter.setOrderByString(byFieldName + " DESC ");
			break;
		case DESC:
			queryFilter.setWhereString(byFieldName + " > @curvalue ");
			queryFilter.setOrderByString(byFieldName + " ASC ");
			break;
		}

		if (!StringUtils.IsNullOrSpace(whereString))
			queryFilter.setWhereString(queryFilter.getWhereString() + " and ("
					+ whereString + ") ");

		queryFilter
				.setWhereParameter(new GlobalParameter[] { new GlobalParameter(
						"@curvalue", ((RowBase) t).getValue(
								GlobalDataType.String, byFieldName)) });
		return getEntity(c, pTable, queryFilter);
	}

	/**
	 * 保存对象
	 * 
	 * @param row
	 */
	@Override
	public void save(ITable pTable, RowBase entity) {
		if (entity == null)
			return;
		if (entity.getIsNew()) {
			optionGuid(pTable, entity);
			executeSql(insertSql(pTable), insertParameter(pTable, entity));
		} else {
			executeSql(updateSql(pTable,entity), updateParameter(pTable, entity));
		}
	}

	/**
	 * 批量保存对象
	 */
	@Override
	public <T extends RowBase> void save(ITable pTable, List<T> entitys) {
		if (entitys == null)
			return;
		List<GlobalParameter[]> insertParameter = new ArrayList<GlobalParameter[]>();
		List<GlobalParameter[]> updateParameter = new ArrayList<GlobalParameter[]>();

		for (RowBase entity : entitys) {
			if (entity.getIsNew()) {
				optionGuid(pTable, entity);
				insertParameter.add(insertParameter(pTable, entity));
			} else {
				updateParameter.add(updateParameter(pTable, entity));
			}
		}

		if (insertParameter != null && insertParameter.size() > 0) {
			executeSql(insertSql(pTable), insertParameter);
		}

		if (updateParameter != null && updateParameter.size() > 0) {
			executeSql(updateSql(pTable), updateParameter);
		}
	}

	/**
	 * 插入对象
	 * 
	 * @param row
	 */
	@Override
	public void insert(ITable pTable, RowBase entity) {
		if (entity == null)
			return;
		optionGuid(pTable, entity);
		executeSql(insertSql(pTable), insertParameter(pTable, entity));
	}

	/**
	 * 插入对象
	 * 
	 * @param row
	 */
	@Override
	public <T extends RowBase> void insert(ITable pTable, List<T> entitys) {
		if (entitys == null)
			return;
		List<GlobalParameter[]> insertParameter = new ArrayList<GlobalParameter[]>();

		for (RowBase entity : entitys) {
			optionGuid(pTable, entity);
			insertParameter.add(insertParameter(pTable, entity));
		}

		if (insertParameter != null && insertParameter.size() > 0) {
			executeSql(insertSql(pTable), insertParameter);
		}
	}

	/**
	 * 更新或添加行
	 * 
	 * @param entity
	 * @return
	 */
	@Override
	public void update(ITable pTable, RowBase entity) {
		if (entity == null)
			return;

		executeSql(updateSql(pTable,entity), updateParameter(pTable, entity));
	}

	/**
	 * 更新或添加行
	 * 
	 * @param entity
	 * @return
	 */
	@Override
	public <T extends RowBase> void update(ITable pTable, List<T> entitys) {
		if (entitys == null)
			return;

		List<GlobalParameter[]> updateParameter = new ArrayList<GlobalParameter[]>();

		for (RowBase entity : entitys) {
			updateParameter.add(updateParameter(pTable, entity));
		}

		if (updateParameter != null && updateParameter.size() > 0) {
			executeSql(updateSql(pTable), updateParameter);
		}
	}

	/**
	 * 更新数据
	 * 
	 * @param updateFilter
	 */
	@Override
	public void update(ITable pTable, IUpdateFilter updateFilter) {
		if (updateFilter == null
				|| StringUtils.IsNullOrSpace(updateFilter.getSetFields()))
			return;

		String sql = updateFilter.toSql(pTable);
		executeSql(sql, updateFilter.getWhereParameter());
	}

	/**
	 * 更新数据
	 * 
	 * @param updateFilter
	 */
	@Override
	public void update(ITable pTable, IUpdateMap updateMap) {
		if (updateMap == null)
			return;
		IUpdateFilter updateFilter = updateMap.toUpdateFilter();
		String sql = updateFilter.toSql(pTable);
		executeSql(sql, updateFilter.getWhereParameter());
	}

	/**
	 * 删除表记录
	 */
	@Override
	public void delete(ITable pTable, IQueryFilter queryFilter) {
		String sql = "delete from " + pTable.getTableName();
		if (queryFilter != null
				&& !StringUtils.IsNullOrSpace(queryFilter.getWhereString())) {
			sql += " where " + queryFilter.getWhereString();
			executeSql(sql, queryFilter.getWhereParameter());
		} else
			executeSql(sql, GlobalParameter.Empty);
	}

	/**
	 * 删除表记录
	 * 
	 * @param queryFilter
	 */
	@Override
	public void delete(ITable pTable, IQueryMap queryMap) {
		IQueryFilter queryFilter = null;
		if (queryMap != null) {
			queryFilter = queryMap.toQueryFilter(pTable);
		}
		delete(pTable, queryFilter);
	}

	/**
	 * 删除表记录
	 */
	@Override
	public void delete(ITable pTable, RowBase row) {
		IQueryFilter queryFilter = null;
		if (row != null) {
			queryFilter = row.toQueryFilter();
		}
		delete(pTable, queryFilter);
	}

	/**
	 * 执行SQL语句返回影响行数
	 * 
	 * @param sqlString
	 * @param para
	 * @return
	 */
	@Override
	public int executeSql(String strSql, GlobalParameter[] para) {
		strSql = this.replaceSql(strSql);
		SqlParameterSource source = ParameterHelper.BindParameter(para);
		logger.info("【executeSql】" + strSql);
		Integer result = jdbcTemplate.execute(strSql, source,
				new executeSqlInt());
		if (result == null) {
			return 0;
		} else {
			return result.intValue();
		}
	}

	private class executeSqlInt implements PreparedStatementCallback<Integer> {
		@Override
		public Integer doInPreparedStatement(PreparedStatement arg0)
				throws SQLException, DataAccessException {
			Integer integer = new Integer(0);
			if (arg0.execute()) {
				integer = 1;
			} else {
				integer = 0;
			}
			return integer;
		}
	}

	/**
	 * 批量执行SQL
	 * 
	 * @param sql
	 * @param paras
	 */
	@Override
	public void executeSql(String sql, List<GlobalParameter[]> paras) {
		sql = replaceSql(sql);
		logger.info("【executeUpdateList】 " + sql);
		List<SqlParameterSource> list = new ArrayList<SqlParameterSource>();
		if (paras != null) {
			for (GlobalParameter[] para : paras) {
				SqlParameterSource source = ParameterHelper.BindParameter(para);
				list.add(source);
			}
		}
		jdbcTemplate.batchUpdate(sql,
				list.toArray(new SqlParameterSource[list.size()]));
	}

	/**
	 * 处理GUID
	 * 
	 * @param pTable
	 * @param entity
	 */
	protected void optionGuid(ITable pTable, RowBase entity) {
		// 处理GUID值
		if (!StringUtils.IsNullOrSpace(pTable.getGUIDFieldName())) {
			Object obj = entity.getValue(pTable.getGUIDFieldName());
			if (obj == null || StringUtils.IsNullOrSpace(obj.toString())) {
				UUID guid = UUID.randomUUID();
				entity.setValue(pTable.getGUIDFieldName(), guid.toString());
			}
		}
	}

	/**
	 * 获取插入SQL语句
	 * 
	 * @param pTable
	 * @return
	 */
	protected String insertSql(ITable pTable) {
		String key = pTable.getMetadataTableName();
		if (insertSqlCache.containsKey(key)) {
			return insertSqlCache.get(key);
		}

		// 插入新行
		String sql = "";
		String fieldList = "";
		for (Field item : pTable.getFields()) {
			if (item.getIsAuto()) {
				fieldList += "[" + item.getFieldName() + "],";
				sql += getAutoSql(pTable, item.getFieldName()) + ",";
			} else {
				fieldList += "[" + item.getFieldName() + "],";
				sql += "@" + item.getFieldName() + ",";
			}
		}

		if (sql.length() > 0) {
			fieldList = fieldList.substring(0, fieldList.length() - 1);
			sql = sql.substring(0, sql.length() - 1);
			sql = "Insert into " + pTable.getTableName() + " (" + fieldList
					+ ") values (" + sql + ")";
		}
		insertSqlCache.put(key, sql);
		return sql;
	}

	/**
	 * 获取插入参数
	 * 
	 * @param pTable
	 * @param entity
	 * @return
	 */
	protected GlobalParameter[] insertParameter(ITable pTable, RowBase entity) {
		// 插入新行
		List<GlobalParameter> whereParameter = new ArrayList<GlobalParameter>();
		Object obj = null;
		for (Field item : pTable.getFields()) {
			if (!item.getIsAuto()) {
				obj = entity.getValue(item.getDataType(), item.getFieldName());
				whereParameter.add(new GlobalParameter("@"
						+ item.getFieldName(), obj, null, item.getDataType()
						.toString()));
			}
		}
		return whereParameter
				.toArray(new GlobalParameter[whereParameter.size()]);
	}

	/**
	 * 获取更新SQL语句
	 * 
	 * @param pTable
	 * @return
	 */
	protected String updateSql(ITable pTable) {
		String key = pTable.getMetadataTableName();
		if (updateSqlCache.containsKey(key)) {
			return updateSqlCache.get(key);
		}

		String sql = "";
		String whereString = getUpdateWhere(pTable);
		for (Field item : pTable.getFields()) {
			if (!item.getIsAuto() && !item.getReadOnly()) {
				sql += "[" + item.getFieldName() + "]=@" + item.getFieldName()
						+ ",";
			}
		}
		if (sql.length() > 0) {
			sql = sql.substring(0, sql.length() - 1);
			sql = "update " + pTable.getTableName() + " set " + sql + " where "
					+ whereString;
		}
		updateSqlCache.put(key, sql);
		return sql;
	}
	
	/**
	 * 获取更新SQL语句：只有entity包含的字段，才会更新，Add BY WINSON
	 * @since 2015-12-25
	 * @param pTable 要更新的表
	 * @param entity 需要更新的字段名称和值
	 * @return  返回更新的SQL语句：如 update table1 set field1=value1,field2=value2...
	 */
	protected String updateSql(ITable pTable,RowBase entity) {
		String sql = "";
		String whereString = getUpdateWhere(pTable);
		List<String> columns = null;
		if(entity!=null){
			columns = Arrays.asList(entity.getColumns());
		}
		for (Field item : pTable.getFields()) {
			if (!item.getIsAuto() && !item.getReadOnly()) {
				if(columns==null || columns.contains(item.getFieldName())){
					sql += "[" + item.getFieldName() + "]=@" + item.getFieldName()
					+ ",";
				}
			}
		}
		if (sql.length() > 0) {
			sql = sql.substring(0, sql.length() - 1);
			sql = "update " + pTable.getTableName() + " set " + sql + " where "
					+ whereString;
		}
		return sql;
	}

	/**
	 * 获取更新条件: 依次根据table的 GUID字段，主键，自增字段来获取更新条件
	 * 
	 * @param pTable
	 * @return
	 */
	public static String getUpdateWhere(ITable pTable) {
		String whereString = "";
		// 根据GUID更新
		String guidField = pTable.getGUIDFieldName();
		if (!StringUtils.IsNullOrSpace(guidField)) {
			whereString += "[" + guidField + "]=@" + guidField;
		} else {
			// 根据主键进行更新
			Field[] keys = pTable.getKeyNoAutoFields();
			if (keys != null && keys.length > 0) {
				for (Field item : keys) {
					whereString += "[" + item.getFieldName() + "]=@"
							+ item.getFieldName();
				}
			} else {
				// 根据自动增值列更新数据
				Field[] autoFields = pTable.getAutoFields();
				if (autoFields != null && autoFields.length > 0) {
					whereString += "[" + autoFields[0].getFieldName() + "]=@"
							+ autoFields[0].getFieldName();
				} else {
					throw new ArgumentException("无法更新当前数据！原因：表"
							+ pTable.getTableName() + "没有主键！");
				}
			}
		}
		return whereString;
	}

	/**
	 * 获取更新参数，entity存在字段，才更新； 并不是table中的所有的字段都更新
	 * @param pTable 
	 * @param entity
	 * @return
	 * 
	 */
	protected GlobalParameter[] updateParameter(ITable pTable, RowBase entity) {

		List<GlobalParameter> whereParameters = getUpdateWhereParameter(pTable,
				entity);
		Object obj = null;
		List<GlobalParameter> setParameter = new ArrayList<GlobalParameter>();
		for (Field item : pTable.getFields()) {
			if (!item.getIsAuto() && !item.getReadOnly()) {
				// add by WINSON: 实体中不包含字段，则该字段不更新
				if(!entity.containsKey(item.getFieldName()))
					continue;
				
				obj = entity.getValue(item.getDataType(), item.getFieldName());
				setParameter.add(new GlobalParameter("@" + item.getFieldName(),
						obj, null, item.getDataType().toString()));
			}
		}
		setParameter.addAll(whereParameters);
		return setParameter.toArray(new GlobalParameter[setParameter.size()]);
	}

	/**
	 * 获取更新条件的参数
	 * 
	 * @param pTable
	 * @param entity
	 * @return
	 */
	public static List<GlobalParameter> getUpdateWhereParameter(ITable pTable,
			RowBase entity) {
		List<GlobalParameter> whereParameters = new ArrayList<GlobalParameter>();
		Object obj;
		// 根据GUID更新
		String guidField = pTable.getGUIDFieldName();
		if (!StringUtils.IsNullOrSpace(guidField)) {
			obj = entity.getValue(guidField);
			whereParameters.add(new GlobalParameter("@" + guidField, obj, null,
					"String"));
		} else {
			// 根据主键进行更新
			Field[] keys = pTable.getKeyNoAutoFields();
			if (keys != null && keys.length > 0) {
				for (Field item : keys) {
					obj = entity.getValue(item.getFieldName());
					if (obj == null)
						throw new ArgumentException("数据更新失败！原因：主键["
								+ item.getFieldName() + "]取值错误！");

					whereParameters.add(new GlobalParameter("@"
							+ item.getFieldName(), obj, null, item
							.getDataType().toString()));
				}
			} else {
				// 根据自动增值列更新数据
				Field[] autoFields = pTable.getAutoFields();
				if (autoFields != null && autoFields.length > 0) {
					obj = entity.getValue(autoFields[0].getFieldName());
					if (obj == null)
						throw new ArgumentException("数据更新失败！原因：自动增值["
								+ autoFields[0].getFieldName() + "]取值错误！");

					whereParameters.add(new GlobalParameter("@"
							+ autoFields[0].getFieldName(), obj, null,
							autoFields[0].getDataType().toString()));
				}
			}
		}
		return whereParameters;
	}

	@Override
	public abstract boolean fieldExist(String name, String fieldName);

	@Override
	public abstract ITableInfo getTableMetadate(String objName);

	@Override
	public abstract boolean objectExist(String objName);

	@Override
	public abstract TableType getObjectType(String objName);

	@Override
	public abstract String[] getObjects(TableType tableType);

	@Override
	public abstract void deleteObject(TableType tableType, String objName);

	@Override
	public abstract void rename(String oldTableName, String newTableName);

	@Override
	public abstract void createTable(ITableInfo tableInfo, String tableName);

	@Override
	public abstract void updateTable(ITableInfo tableInfo, String tableName);

	@Override
	public abstract void createOrUpdateView(String viewName, String selectSql,
			boolean isStatic);

	/**
	 * 关闭对象
	 */
	@Override
	public void close() {
		if (dataSource != null) {
			try {
				dataSource.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}